﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Threading;
using System.Globalization;
using System.Data;
using System.Text.RegularExpressions;
using System.Data.SqlClient;
using System.Configuration;

public partial class Content_js_AutoComplete : System.Web.UI.Page
{
    [System.Web.Services.WebMethod()]
    public static string CenterPopUpToClient(string strSearchKey, string strQuerystring, string strSearch)
    {
        Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
        string sText = "";

        DataRow row_config = null;
        DataSet ds = new DataSet();

        ds.ReadXml(HttpContext.Current.Server.MapPath("~/XML/ConfigAutoComplete.xml"));
        ds.Tables[0].PrimaryKey = new DataColumn[] { ds.Tables[0].Columns["key"] };

        if (ds.Tables[0].Rows.Count > 0)
        {
             row_config = ds.Tables[0].Rows.Find(strSearchKey);
             if (row_config != null)
             {
                 string sSql = "";
                 string sOrderby = "";
                 string[] arrParameter = null;
                 string[] arrSearch = null;
                 string[] arrLike = null;
                 string[] arrSql = null;

                 List<SqlParameter> arrSqlParams = new List<SqlParameter>();

                 arrSql = Regex.Split(row_config["statement"].ToString(), "order by", RegexOptions.IgnoreCase);

                 sSql = arrSql[0];

                 if (arrSql.Length == 2)
                     sOrderby = " order by " + arrSql[1];

                 arrSearch = Array.ConvertAll(row_config["search_with_text_box"].ToString().Split(','), p => p.Trim());

                 if (row_config.Table.Columns.Contains("search_with_like"))
                     arrLike = Array.ConvertAll(row_config["search_with_like"].ToString().Split(','), p => p.Trim());

                 if (arrLike != null)
                 {
                     if (arrLike.Length > 0)
                     {
                         if (arrLike[0] == "0")
                             sSql += sSql = " and " + arrSearch[0] + " = @SearchData";
                         else if (arrLike[0] == "1")
                             sSql += sSql = " and " + arrSearch[0] + " like @SearchData + '%' ";
                            else if (arrLike[0] == "2")
                             sSql += sSql = " and " + arrSearch[0] + " like '%' + @SearchData + '%' ";
                     }
                 }

                 arrSqlParams.Add(new SqlParameter("@SearchData", strSearch.Trim()));

                 if (row_config["parameter"] != DBNull.Value && !string.IsNullOrEmpty(row_config["parameter"].ToString()))
                 {
                     if (!string.IsNullOrEmpty(strQuerystring))
                     {
                         arrParameter = Array.ConvertAll(row_config["parameter"].ToString().Split(','), p => p.Trim());

                         var parsedQuery = HttpUtility.ParseQueryString(strQuerystring);

                         foreach (string strTemp in arrParameter)
                         {
                             if (string.IsNullOrEmpty(parsedQuery[strTemp]))
                                 arrSqlParams.Add(new SqlParameter("@" + strTemp, string.Empty));
                             else
                                 arrSqlParams.Add(new SqlParameter("@" + strTemp, parsedQuery[strTemp]));
                         }
                     }
                 }

                 if (sOrderby != "")
                     sSql = sSql + sOrderby;

                 DataTable dtAjax = new DataTable();

                 dtAjax = SqlDataTable(sSql.Trim(), arrSqlParams.ToArray());

                 if (dtAjax != null && dtAjax.Rows.Count >= 1)
                 {
                    string[] arrColumn = null;
                    DataRow selRowCenter = null;
                    selRowCenter = dtAjax.Rows[0];

                    if (row_config["select_return_back"] != DBNull.Value)
                    {
                        arrColumn = Array.ConvertAll(row_config["select_return_back"].ToString().Split(','), p => p.Trim());

                        foreach (string strTemp in arrColumn)
                        {
                            sText += selRowCenter[strTemp].ToString() + "$";
                        }

                        sText = sText.Substring(0, sText.Length - 1);
                        if (sText.IndexOf('$') == -1)
                            sText = sText + "$";
                    }
                 }
                 else
                 {
                     sText = "NoFind";
                 }
             }
        }
        return sText;
    }

    public static DataTable SqlDataTable(string sSql, params SqlParameter[] Parameters)
    {
        DataTable dt = new DataTable();
        SqlConnection Conn = null;
        try
        {
            Conn = new SqlConnection(ConfigurationManager.ConnectionStrings["IFRS_WEB"].ConnectionString);
            Conn.Open();
            SqlCommand Cmd = new SqlCommand(sSql, Conn);
            Cmd.CommandType = CommandType.Text;
            if (Parameters != null)
            {
                foreach (SqlParameter param in Parameters)
                {
                    Cmd.Parameters.Add(param);
                }
            }
            SqlDataReader dr = Cmd.ExecuteReader();
            dt.Load(dr);
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            Conn.Close();
            Conn.Dispose();
        }
        return dt;
    }

}